package com.maycurobj.repository.bim;

import com.maycurobj.entity.bim.Tbtacc;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

/**
 * tbtacc 持久类
 */
@Repository
public interface TbtaccRepository extends JpaRepository<Tbtacc, String> {

    /**
     * 获取统一系统中对应的钉钉userid by hrID
     * @param userid
     * @return
     */
    @Query(nativeQuery = true, value= "select tacc_guid from tb_tacc where susr_id=(select susr_id from tb_tacc where tacc_guid=:userid and tsys_id='20190805194802425-9F5C-60807707C')  and tsys_id='20190809091729548-B943-3EE3CBB13' and tacc_process_status='FINISHED'")
    String getDingUserID(@Param(value = "userid") String userid);

    /**
     * 获取统一系统中对应钉钉的userid by crm工号
     */
    @Query(nativeQuery = true, value= "select tacc_guid from tb_tacc where susr_id=(select susr_id from tb_tacc where tacc_guid=:workcode and tsys_id='20190808101738409-768A-F513D0C09' and tacc_process_status='FINISHED') and tsys_id='20241111124028505-D4DC-F7789D023' and tacc_process_status='FINISHED'")
    String getDingUserIdByCrmworkcode(@Param(value = "workcode") String workcode);

    /**
     * 获取统一系统中 对应 crmuser by 工号
     * @param workcode
     * @return
     */
    @Query(nativeQuery = true,value="SELECT " +
            "tacc_username  " +
            "FROM " +
            "tb_tacc  " +
            "WHERE " +
            "tsys_id = '20190715155816809-CE79-BF6AA93B3'  " +
            "AND SUSR_ID IN ( " +
            "SELECT " +
            "SUSR_ID  " +
            "FROM " +
            "tb_tacc, " +
            "tb_tacc_x_ding  " +
            "WHERE  tacc_is_disabled <> 1 and " +
            "tb_tacc.TACC_ID = tb_tacc_x_ding.TACC_ID  " +
            "AND tb_tacc_x_ding.X_JOBNUMBER = :workcode )")
    String getCrmUsernameByWorkcode(@Param(value = "workcode") String workcode);


    /**
     * 获取统一系统中 对应的 钉钉id 找到 crm username 编号
     * @param guid
     * @return
     */
    @Query(nativeQuery = true,value="SELECT " +
            "tacc_username  " +
            "FROM " +
            "tb_tacc  " +
            "WHERE " +
            "tsys_id = '20190715155816809-CE79-BF6AA93B3'  " +
            "AND SUSR_ID IN ( " +
            "SELECT " +
            "SUSR_ID  " +
            "FROM " +
            "tb_tacc  " +
            "WHERE " +
            "tsys_id = '20190809091729548-B943-3EE3CBB13'  " +
            "AND TACC_GUID =:guid)")
    String getIamUsernameByDingId(@Param(value = "guid") String guid);

    /**
     * 删除奥哲所有 待办 20221221225104151-8739-C9463D969  每刻:20221221225051709-0798-975D91438
     */
    @Modifying
    @Query(nativeQuery=true,value = "DELETE t1,t2 from tb_trsc_x_biosancrm_biosancrmres as t1 inner join tb_trsc as t2 on t1.trsc_id=t2.trsc_id where t1.X_BACKLOGTYPE='20221221225104151-8739-C9463D969'")
    Integer deleteIamTodoByAozhe();

}
